|
递归查询递归查询的语法使用语句SQL语句即可把整个递归树全部查询出来。使用LEVEL关键字和LPAD函数，在OUTPUT中显示树形层次 Oracle 递归查询
递归查询
递归查询的语法SELECT [LEVEL], column, expr...FROM table[WHERE condition(s)][START WITH condition(s)][CONNECT BY PRIOR condition(s)] ;
使用语句SQL语句即可把整个递归树全部查询出来。查询从King开始，从上往下的各级员工
SELECT last_name||' reports to '||PRIOR last_name "Walk Top Down"FROM employeesSTART WITH last_name = 'King'CONNECT BY PRIOR employee_id = manager_id ;
查询从101开始，从下往上的各级员工
SELECT employee_id, last_name, job_id, manager_idFROM employeesSTART WITH employee_id = 101CONNECT BY PRIOR manager_id = employee_id ;
使用LEVEL关键字和LPAD函数，在OUTPUT中显示树形层次SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chartFROM employeesSTART WITH last_name='King'CONNECT BY PRIOR employee_id=manager_id
|